clear
set more off


use "${wd}/nlrb/nlrb7799_02.dta",clear


drop  maxdate closed_date election_date sic ///
	num idnlrb var* fiscalyear elect_id ///
	region type docket size_cat vote_los* union_l1* union_l2* ///
	duplic union_partic union_incumb rectype union_w_code address ///
	union_code casenum cntytext localnum pet_type employer unionname dups* ///
	checker temp tot year_clos month_clos year_elect month_elect recent_elect 


/////*****************Method for fixing missing county codes*****************/////

// Missing data

ren cntycode unit_location_county

keep if unit_location_county=="" | unit_location_county=="000" ///
	| unit_location_county=="-89" | unit_location_county=="-97" 
	
// 1029 observations with problematic county codes
duplicates tag nlrb_id,g(dups1)
duplicates tag nlrb_id unit,g(dups2)

sort dups*
order dups* nlrb_id

// verified that all duplicates have the same county in 1961-1998 file

duplicates drop nlrb_id,force
drop dups*

count

*save "${temp}/geo_data_problems.dta",replace

preserve
	use "${temp}/nlrb_representation_1961_1998.dta",clear
	duplicates drop nlrb_id state county_name,force
	keep nlrb_id state county_name
	save "${temp}/nlrb_representation_1961_1998_2.dta",replace
restore

merge 1:m nlrb_id using "${temp}/nlrb_representation_1961_1998_2.dta"
drop if _merge == 2


// CHECK THESE LATER
preserve
	keep if _merge==1 // 175 cannot be merged
	drop _merge
	sum eligible // 12,959 eligible workers cannot be merged in
	disp `r(sum)'
	
	save "${temp}/non_merge.dta",replace
restore

keep if _merge==3
drop _merge
*save "${temp}\geo_data_problems_2.dta",replace

drop state1

merge m:1 state county_name using "${temp}/county_merge.dta"

// Manual coding of certain missing
replace cntycode = "057" if county_name == 295725
replace state1 = "FL" if county_name == 295725
replace cntycode = "173" if county_name == 485038
replace state1 = "KS" if county_name == 485038
replace cntycode = "009" if county_name == 659450
replace state1 = "MN" if county_name == 659450
replace cntycode = "003" if county_name == 1213690
replace state1 = "PA" if county_name == 1213690

// CHECK THESE LATER
preserve
	keep if _merge==1 // 38 cannot be merged
	drop _merge
	sum eligible // 2042 eligible workers cannot be merged in
	disp `r(sum)'
	
	append using "${temp}/non_merge.dta"
	save "${temp}/non_merge.dta",replace
restore

keep if _merge == 3
drop _merge

keep nlrb_id cnty* state1

save "${temp}/nlrb_county_merge.dta",replace




